package top.kaoshanji.r6ch13c;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.*;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author kaoshanji
 * ^_^
 * create time 2025/5/11 19:01
 */
@Repository
public class ForumDao {

    private JdbcTemplate jdbcTemplate;

    // 添加 t_forum 的SQL语句
    private static final String ADD_FORUM_SQL = " INSERT INTO t_forum(forum_name,forum_desc) VALUES(?,?) ";

    @Autowired
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    // 插入方式1：参数通过数组传递，每个参数对应SQL语句里的一个?
    public void addForumV1(Forum forum) {
        Object[] params = new Object[] {forum.getForumName(), forum.getForumDesc()};
        jdbcTemplate.update(ADD_FORUM_SQL, params);
    }

    // 插入方式2：显式指定每个占位符所对应的字段数据类型
    public void addForumV2(Forum forum) {
        Object[] params = new Object[] {forum.getForumName(), forum.getForumDesc()};
        int[] paramsType = new int[] {Types.VARCHAR, Types.VARCHAR};

        jdbcTemplate.update(ADD_FORUM_SQL, params, paramsType);
    }

    // 插入方式3：通过创建PreparedStatementSetter对象绑定参数的匿名内部类回调
    // 为了Forum可以在内部类中使用，必须声明为 final
    // PreparedStatement 参数索引从1开始
    public void addForumV3(final Forum forum) {

        jdbcTemplate.update(ADD_FORUM_SQL, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setString(1, forum.getForumName());
                ps.setString(2, forum.getForumDesc());
            }
        });

    }

    // 插入方式4：返回数据库的表自增主键值
    public void addForumV4(Forum forum) {

        // 创建一个主键持有者
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
                PreparedStatement ps = con.prepareStatement(ADD_FORUM_SQL, Statement.RETURN_GENERATED_KEYS);
                ps.setString(1, forum.getForumName());
                ps.setString(2, forum.getForumDesc());
                return ps;
            }
        }, keyHolder);

        // 从主键持有者中获得主键
        int forumId = keyHolder.getKey().intValue();

        System.out.println("...获取自增主键：" + forumId + "...");
    }

    // 批量更改数据
    public void addForumBatch(final List<Forum> forumList) {
        jdbcTemplate.batchUpdate(ADD_FORUM_SQL, new BatchPreparedStatementSetter() {

            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                Forum forum = forumList.get(i);

                ps.setString(1, forum.getForumName());
                ps.setString(2, forum.getForumDesc());
            }

            @Override
            public int getBatchSize() {
                return forumList.size(); // 该批次的记录数
            }
        });

    }

    // 使用RowCallbackHandler查询单条结果集
    public Forum getForumV1(final int forumId) {
        String sql = " SELECT forum_name,forum_desc FROM t_forum WHERE forum_id=? ";
        final Forum forum = new Forum();
        final Object[] params = new Object[]{forumId};

        // 将结果集数据行中的数据抽取到forum对象中
        jdbcTemplate.query(sql, params, new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet rs) throws SQLException {
                forum.setForumId(forumId);
                forum.setForumName(rs.getString("forum_name"));
                forum.setForumDesc(rs.getString("forum_desc"));
            }
        });

        return forum;
    }

    // 使用RowCallbackHandler查询多条结果集
    public List<Forum> getForumListV1(final int fromId, final int toId) {
        String sql = " SELECT forum_id,forum_name,forum_desc FROM t_forum WHERE forum_id between ? and ? ";

        final List<Forum> forumList = new ArrayList<>();
        final Object[] params = new Object[]{fromId, toId};

        jdbcTemplate.query(sql, params, new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet rs) throws SQLException {
                Forum forum = new Forum();
                forum.setForumId(rs.getInt("forum_id"));
                forum.setForumName(rs.getString("forum_name"));
                forum.setForumDesc(rs.getString("forum_desc"));

                forumList.add(forum);
            }
        });

        return forumList;
    }

    // 使用RowCallbackHandler查询多条结果集
    public List<Forum> getForumListV2(final int fromId, final int toId) {
        String sql = " SELECT forum_id,forum_name,forum_desc FROM t_forum WHERE forum_id between ? and ? ";

        final Object[] params = new Object[]{fromId, toId};

        return jdbcTemplate.query(sql, params, new RowMapper<Forum>() {

            @Override
            public Forum mapRow(ResultSet rs, int rowNum) throws SQLException {
                Forum forum = new Forum();
                forum.setForumId(rs.getInt("forum_id"));
                forum.setForumName(rs.getString("forum_name"));
                forum.setForumDesc(rs.getString("forum_desc"));

                return forum;
            }
        });

    }

    // 单列单行
    public int getForumCount() {
        String sql = " SELECT count(*) FROM t_forum ";
        return jdbcTemplate.queryForObject(sql, int.class);
    }


}
